﻿using System.Dynamic;

namespace Admin.NET.Core.Btms;

/// <summary>
/// 直接执行sql语句，这里只放与业务无关的公共的方法
/// </summary>
public class DataBaseManager : IScoped
{
    private readonly ISqlSugarClient _db;
    private readonly IServiceScope _serviceScope;

    // 旧的写法
    //public DataBaseManager(ISqlSugarClient db)
    //{
    //    _db = db;
    //}

    public DataBaseManager(IServiceScopeFactory scopeFactory)
    {
        _serviceScope = scopeFactory.CreateScope();
        _db = _serviceScope.ServiceProvider.GetRequiredService<ISqlSugarClient>();
    }

    /// <summary>
    /// 基于sql语句进行参数化查询一个表格，并且返回json格式的数组
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameter"></param>
    public List<dynamic> Query2List(string sql, List<SugarParameter> parameter)
    {
        var table = Query2DataTable(sql, parameter);
        return DataTable2List(table);
    }

    /// <summary>
    /// 基于sql语句进行参数化查询一个表格，并且返回json格式的数组
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameter"></param>
    public (List<dynamic> columns, List<dynamic> data) Query2Tabulator(string sql, List<SugarParameter> parameter)
    {
        var table = Query2DataTable(sql, parameter);
        return DataTable2Tabulator(table);
    }


    /// <summary>
    /// 基于sql语句进行参数化查询一个表格，并且返回json格式的数组
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameter"></param>
    public DataTable Query2DataTable(string sql, List<SugarParameter> parameter)
    {
        // 1,SQL预处理
        if (string.IsNullOrWhiteSpace(sql))
        {
            return new DataTable();
        }

        var beginIndex = sql.IndexOf("--declare-end");
        if (beginIndex > 0)
        {
            sql = sql.Substring(beginIndex + 1);
        }
        beginIndex = sql.IndexOf("-- declare-end");
        if (beginIndex > 0)
        {
            sql = sql.Substring(beginIndex + 1);
        }
        sql = sql.Replace("\\r\\n", Environment.NewLine); //windows环境
        sql = sql.Replace("\\n", Environment.NewLine); //linux环境
        sql = sql.Replace("\\r", Environment.NewLine); //macos环境
        sql = sql.Replace("\\t", "  "); // \t处理为空格

        if (string.IsNullOrWhiteSpace(sql))
        {
            return new DataTable();
        }

        // 2,参数化查询
        return _db.Ado.GetDataTable(sql, parameter);
    }

    /// <summary>
    /// 把DataTable转换为Tabulator的json格式
    /// </summary>
    public List<dynamic> DataTable2List(DataTable table)
    {
        // 数据列表
        var data = new List<dynamic>();
        foreach (DataRow row in table.Rows)
        {
            dynamic dynamicObject = new ExpandoObject();
            var dictionary = (IDictionary<string, object>)dynamicObject;

            foreach (DataColumn column in table.Columns)
            {
                dictionary[column.ColumnName] = row[column];
            }

            data.Add(dynamicObject);
        }

        return data;
    }

    /// <summary>
    /// 把DataTable转换为Tabulator的json格式
    /// </summary>
    public (List<dynamic> columns, List<dynamic> data) DataTable2Tabulator(DataTable table)
    {
        // columns列表
        var columns = new List<dynamic>();
        foreach (DataColumn column in table.Columns)
        {
            columns.Add(new { title = column.ColumnName, field = column.ColumnName, type = "text" });
        }

        // 数据列表
        var data = new List<dynamic>();
        foreach (DataRow row in table.Rows)
        {
            dynamic dynamicObject = new ExpandoObject();
            var dictionary = (IDictionary<string, object>)dynamicObject;

            foreach (DataColumn column in table.Columns)
            {
                dictionary[column.ColumnName] = row[column];
            }

            data.Add(dynamicObject);
        }

        return (columns, data);
    }

    /// <summary>
    /// 把DataTable转换为Tabulator的json格式
    /// </summary>
    //    public (string columnsJson, string dataJson) DataTable2Tabulator(DataTable table)
    //    {
    //        // 定义columns列表
    //        var columns = new List<dynamic>();
    //        foreach (DataColumn column in table.Columns)
    //        {
    //            columns.Add(new { title = column.ColumnName, field = column.ColumnName, type = "text" });
    //        }

    //        // 将columns列表转换为JSON字符串
    //        string columnsJson = JsonConvert.SerializeObject(columns);

    //        // 3,转换为json格式的数组
    //        var dataJson = JsonConvert.SerializeObject(table, new DataTableConverter());
    //        return (columnsJson, dataJson);
    //    }
}
